﻿<SQLFunctionSpec
	xmlns="bb_appfx_sqlfunction"
	xmlns:common="bb_appfx_commontypes" 
	ID="51514c9c-511e-41fa-81bf-a13f86f46c65"
	Name="USR_UFN_PLEDGEREVENUEID_FROMPAYMENTREVENUEID"
	Description="Given a payment, return the revenue id of the pledge it is for (may be null)"
	Author="Blackbaud Professional Services"
	DBFunctionName="USR_UFN_PLEDGEREVENUEID_FROMPAYMENTREVENUEID"
	>

	<CreateFunctionSQL>
		<![CDATA[
create function dbo.USR_UFN_PLEDGEREVENUEID_FROMPAYMENTREVENUEID(@PAYMENTREVENUEID uniqueidentifier)
returns uniqueidentifier
with execute as caller
as begin
  return(
    select pledged.id
    from
      revenue rev
      inner join
      revenuesplit spl
      on spl.revenueid = rev.id
      inner join
      installmentpayment insp
      on insp.PAYMENTID = spl.ID
      inner join
      revenue pledged
      on pledged.id = insp.PLEDGEID
    where rev.id = @paymentrevenueid
    );
end
		]]>
	</CreateFunctionSQL>
</SQLFunctionSpec>